package com.dao.database;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Properties;
import java.util.Vector;

import org.apache.log4j.Logger;

import com.dao.database.exceptions.DataAccessException;

public class PredictableObjectDao implements GeneralObjectDAO {

	private final static Logger LOGGER = Logger.getLogger(PredictableObjectDao.class
			.getName());
	private String dbConfig = "config.txt";
	private Connection connection;
	
	public PredictableObjectDao() throws IOException,DataAccessException{
		try {
			LOGGER.trace("constructor: PredictableObjectDao()");
			loadProperties(dbConfig);
		} catch (FileNotFoundException e) {
			LOGGER.error("Couldont open property file: " + dbConfig, e);
			throw e;
		} catch (IOException e) {
			LOGGER.error("Couldn't load properties", e);
			throw e;
		} catch (SQLException e) {
			LOGGER.error("Couldn't establish connection to the database",e);
			throw new DataAccessException("Couldn't establish connection to the database",e);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	
	private void loadProperties(String dbFileName)throws FileNotFoundException,
	IOException, SQLException, ClassNotFoundException {
		LOGGER.trace("method: loadProperties("+dbFileName+")");
		FileReader fr1 = new FileReader(dbFileName);
		BufferedReader br1 = new BufferedReader(fr1);
		Properties prop1 = new Properties();
		prop1.load(br1);
		Class.forName("oracle.jdbc.driver.OracleDriver"); 
		connection = DriverManager.getConnection(
				"jdbc:oracle:thin:@" + prop1.getProperty("host") + ":"
						+ prop1.getProperty("database"),
				prop1.getProperty("user"), prop1.getProperty("password"));
		
	}
	
	public void createPredictableObject(String name,String type,String variableFileName) throws DataAccessException, IOException {
		LOGGER.trace("method: createPredictableObject"+name+")");
		String sql="insert into Predictable_Object(idObject,name,type)" +
				" values(predictable_object_seq.nextval,"+"'"+name.toUpperCase()+"','"+type+"')" ;
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
			loadVariableList(name, variableFileName);
		}catch(DataAccessException e){
			LOGGER.error("Catch error from private method findObjId(name)"+e.getMessage());
			throw e;
		}
		catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		
		
	}

	@Override
	public void deleteObject(int objId) throws DataAccessException {
		LOGGER.trace("method: deleteObject("+ objId+")");
		String sql1="delete from predictable_object where idobject="+objId;
		String sql2="delete from variable where idobject="+objId;
		try {
			Statement stmt=connection.createStatement();
			stmt.executeUpdate(sql1);
			stmt.executeUpdate(sql2);
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql1+" or "+sql2, e);
			throw new DataAccessException("Couldn't create or execute statement "+sql1+" or "+sql2, e);
		}

	}
	
	@Override
	public void deleteAllObjects() throws DataAccessException {
		LOGGER.trace("method: deleteAllObjects()");
		String sql1="delete from predictable_object";
		String sql2="delete from variable";
		try {
			Statement stmt=connection.createStatement();
			stmt.executeUpdate(sql1);
			stmt.executeUpdate(sql2);
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql1+" or "+sql2, e);
			throw new DataAccessException("Couldn't create or execute statement "+sql1+" or "+sql2, e);
		}
		
	}

	@Override
	public PredictableObject findObject(int objId) throws DataAccessException {
		LOGGER.trace("method: findObject("+objId+")");
		String sql="select * " +
				"from predictable_object where idobject='" +
				objId+"'";
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			rs.next();
			PredictableObject object= new PredictableObject(objId,rs.getString(2),rs.getString("type"),
					new HashMap<String,PredictableObject.Variable>());
			stmt.close();
			return loadPredictableObjectVariables(object);
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
	}

	@Override
	public PredictableObject findObject(String name) throws DataAccessException {
		LOGGER.trace("method: findObject("+name+")");
		String sql="select * " +
				"from predictable_object where name='" +
				name.toUpperCase()+"'";
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			rs.next();
			PredictableObject object= new PredictableObject(rs.getInt(1),name,rs.getString("type"),
					new HashMap<String,PredictableObject.Variable>());
			stmt.close();
			return loadPredictableObjectVariables(object);
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
	}

	@Override
	public Vector<String> findAllNamesOfObjects()
			throws DataAccessException {
		LOGGER.trace("method: findAllNamesOfObjects()");
		String sql="select name from predictable_object";
		Vector<String> objects = new Vector<String>();
		try {
			Statement stmt=connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next()){
				objects.add(rs.getString("name"));
			}
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		return objects;
	}
	public void createVariable(int ObjId, String name, String unit,
			String description) throws DataAccessException {
		LOGGER.trace("method: createVariable("+ObjId+","+name+","+unit+","+
			description+")");
		Statement stmt;
		String insertStatement="insert into Variable (idObject,idElement,name,unit,description) " +
				"values ("+ObjId+", variable_seq.NEXTVAL,'"+name.toUpperCase()+"','"+unit.toUpperCase()+"','"+description.toUpperCase()+"')";
		try {
			stmt = connection.createStatement();
			stmt.executeQuery(insertStatement);
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Coudlnt create or execute statement"+insertStatement, e);
			throw new DataAccessException("Coudlnt create or execute statement"+insertStatement,e);
		}		
	}

	public void deleteVariable(int objId, int varId) throws DataAccessException {
		LOGGER.trace("method: deleteVariable("+objId+","+ varId+")");
		String sql="delete from variable where idobject="+objId+
				" and idelement="+varId;
		try {
			Statement stm=connection.createStatement();
			stm.executeUpdate(sql);
			stm.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		
	}

	public Vector<String> findAllNamesOfObjectVariables(int objId) throws DataAccessException {
		LOGGER.trace("method: findAllNamesOfObjectVariables("+objId+")");
		String sql="select name from variable where idobject="+objId;
		Vector<String> variables = new Vector<String>();
		try {
			Statement stmt=connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next()){
				variables.add(rs.getString("name"));
			}
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		return variables; 
	}
	
	private PredictableObject loadPredictableObjectVariables(PredictableObject object) throws DataAccessException {
		LOGGER.trace("private method: loadPredictableObjectVariables("+object+")");
		String sql= "Select * from variable where idObject="+object.getObjId();
		Statement stmt;
		try {
			stmt = connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while (rs.next()==true){
				object.putVariable(rs.getInt("idElement"),rs.getString("name"), rs.getString("unit"), rs.getString("description"));
			}
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		return object;
	}
	private int findObjId(String name){
		LOGGER.trace("private method: findObjId("+name+")");
		String sql="select idobject " +
				"from predictable_object where name='" +
				name.toUpperCase()+"'";
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			rs.next();
			int result= rs.getInt("idobject");
			stmt.close();
			return result;
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
	}
	private void loadVariableList(String objName, String fileName) throws IOException {
		LOGGER.trace("private method: loadVariableList("+objName+","+fileName+")");
		int objId=findObjId(objName);
		FileReader fr = new FileReader(fileName);
		BufferedReader br2 = new BufferedReader(fr);
		Properties prop2 = new Properties();
		prop2.load(br2);
		Iterator<Object> iterator=prop2.keySet().iterator();
		while(iterator.hasNext()){
			String key=(String) iterator.next();
			String [] value=prop2.getProperty(key).split(",");
			createVariable(objId, key, value[1], value[2]);
		}
		
	}
	
}
